import javax.swing.*;
import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Util {
    /**
     * 加载驱动
     *
     * @return
     */
    public static Connection getConnection() {
// 1. 加载驱动
        Connection con = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/atm?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "PGmdfkone1");
            System.out.println("con:" + con);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 封装close
     *
     * @param statement
     * @param con
     */
    public static void close(Statement statement, Connection con) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 重载查询close
     * @param statement
     * @param con
     */
    public static void  close(Statement statement, Connection con,ResultSet resultSet) {
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 增加数据
     */
    public static void insert(String sql) {
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2.获取执行SQL语句对象
            statement = con.createStatement();
            // String sql = "insert into t_user(u_account,u_password,u_balance) values ('" + account + "','" + password + "'," + balance + ")";
            //3.执行SQL语句
            int row = statement.executeUpdate(sql);
            System.out.println("受影响的行数：" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }

    /**
     * 查询
     * 和添加的区别在于查询会返回一个结果集，返回集需要关闭
     */
//    public static ArrayList query() {
//        //1.创建连接
//        Connection con = getConnection();
//        Statement statement = null;
//        ResultSet rs = null;
//        List userList = new ArrayList();
//        try {
//            statement = con.createStatement();
//            String sql = "select u_id,u_account,u_password,u_balance from t_user";
//            rs = statement.executeQuery(sql);
//            while (rs.next()){
//                //封装为集合
//                User user = new User();
//                user.setId(rs.getInt("u_id"));
//                user.setAccount(rs.getString("u_account"));
//                user.setPassword(rs.getString("u_password"));
//                user.setBalance(rs.getInt("u_balance"));
//                userList.add(user);
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }finally {
//            close(statement,con,rs);
//        }
//        return (ArrayList) userList;
//    }
    public static Image getImage(String path){
        return new ImageIcon(Util.class.getResource(path)).getImage();
    }

    public static ImageIcon getIcon(String path){
        return new ImageIcon(Util.class.getResource(path));
    }
    public User getById(int id) {
        Connection con = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        User users = null;
        String sql = "select * from t_users where u_id=?";
        try {
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                users = new User();
                users.setId(rs.getInt("u_id"));
                users.setAccount(rs.getString("u_account"));
                users.setPassword(rs.getString("u_pwd"));
                users.setBalance(rs.getDouble("u_blance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(preparedStatement,con, rs);
        }
        return users;
    }

    /**
     * 根据用户名查询用户对象
     *
     * @param username
     * @return
     */
    public User getByUsernameAndPwd(String username,String pwd){
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_student where u_name=? and u_pwd=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,username);
            statement.setObject(2,pwd);
            rs = statement.executeQuery();
            if(rs.next()){
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_account"));
                user.setPassword(rs.getString("u_pwd"));
                user.setBalance(rs.getDouble("u_blance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(statement,con,rs);
        }
        return user;
    }
    public User getByUsername(String username){
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_name=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,username);
            rs = statement.executeQuery();
            if(rs.next()){
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_name"));
                user.setPassword(rs.getString("u_pwd"));
                user.setBalance(rs.getDouble("u_blance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(statement,con,rs);
        }
        return user;
    }


    public void update(User loginUser,User otherUser) {
        // 1. 创建连接
        Connection con = getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update t_account set u_pwd=?,u_blance=? where u_id=?";
        try {
            // 2. 获取执行SQL语句对象
            con.setAutoCommit(false);
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setObject(1, loginUser.getPassword());
            preparedStatement.setObject(2, loginUser.getBalance());
            preparedStatement.setObject(3, loginUser.getId());
            // 3. 执行SQL语句
            preparedStatement.executeUpdate();
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setObject(1, otherUser.getPassword());
            preparedStatement.setObject(2, otherUser.getBalance());
            preparedStatement.setObject(3, otherUser.getId());
            // 3. 执行SQL语句
            preparedStatement.executeUpdate();
            con.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            // 4. 关闭连接
            close(preparedStatement,con);
        }
    }

    public void update(User User) {
        // 1. 创建连接
        Connection con = getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "update t_account set u_pwd=?,u_blance=? where u_id=?";
        try {
            // 2. 获取执行SQL语句对象
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setObject(1, User.getPassword());
            preparedStatement.setObject(2, User.getBalance());
            preparedStatement.setObject(3, User.getId());
            // 3. 执行SQL语句
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4. 关闭连接
            close(preparedStatement,con);
        }
    }
}
